const CFG = require('../config');
const FUN = require('../comm/func');
const EC = require('../comm/err-code');
const timLib = require('../comm/time-lib');
const mysql = require('mysql');
const tbPolicy = require('./tb_policy');
const PCY_ID_TAB = 'policy_id';
const DAT_ALARM_TAB = 'dat_alarm';
const DAY_MSEC = 86400*1000;

// 连接池操作数据库
module.exports = class ModelData
{
  // 连接对象
  static pool = null;
  static first= true;

  static pool_conn() // 连接一次就行
  {
    if(this.first) {
      this.pool=mysql.createPool(CFG.MySql2);
      this.first=false;
    }
  } // end pool_conn()

  static query(sql,params=[])
  {
    this.pool_conn();
    return new Promise((resolve,reject)=>{
      this.pool.getConnection((err,conn)=>{
        if (err) {
          reject(err);
        } else {
          conn.query(sql, params, (err,results)=>{
            if(err) reject(err);
            else resolve(results);
          })
          conn.release();
        }
      })//end Model.pool...
    })
  } // end query()

  // 如果不存在就创建新表
  static createTab(uid, table, field_conf, comment)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    return new Promise((resolve,reject)=>{
      let sql="CREATE TABLE IF NOT EXISTS `"+table+"` "+field_conf+" COMMENT='"+comment+"';";
      this.query(sql).then(res=>{
        resolve(res);
      }).catch(err=>{
        console.log('Creat db faild: '+err.message);
        reject(err);
      })
    })
  }

  // 新增一条记录,then返回插入的id
  static newRcd(uid, table, names, params)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    return new Promise((resolve,reject) => {
      let qt=names.replace(/[^,]+/g,'?');//得到相同数量的问号
      let sql='INSERT INTO `'+table+'` ('+names+') VALUES('+qt+')';
      this.query(sql, params).then(res=>{
        resolve(res.insertId);
      }).catch(async(err)=>{
        if (err.errno == EC.ER_NO_REFERENCED_ROW_2) { // 外键不对，新建主表记录，todo 权限是否允许
          let nameArr = names.split(',');
          for (let k in nameArr) { // 轮询属性，找到policy_id字段
            if (nameArr[k] != 'policy_id') continue;
            let policy_id = params[k];
            const tbPolicy = require('./tb_policy'); // 此处引用头部会报not is a function错，不清楚为什么
            let pcyItem = await tbPolicy.findById(uid, policy_id);
            if (pcyItem == null) return;
            this.recordPcyId(uid, policy_id, pcyItem.prod_id).then(id=>{
              console.log('Patch new foreign key: policyId[%d] -> uid[%d] prod_id[%d]', policy_id, uid, pcyItem.prod_id);
            })
            break;
          } // end for
        } // end if err.errno == ER_NO_REFERENCED_ROW_2
        console.log('NewReg insert faild:'+err.message);
        reject(err);
      })
    })
  }

  // 将条件数组转换为表达式
  static conditonExpHandler(table, policyIds, condArr=[])
  {
    // 条件处理
    let whereArr = [];
    for (let f in condArr) {
      let ff = f.split(':'); // 字段名:条件[IN,=,>,<]
      if (ff.length == 2) { // ff[0] 字段名, ff[1] 条件, condArr[f] 值
        if (ff[1] == 'IN' || ff[1] == 'in') {
          whereArr.push('`'+ff[0]+'` IN ('+condArr[f]+')');
        } else if (ff[1] == 'NOTIN' || ff[1] == 'notin') {
          whereArr.push('`'+ff[0]+'` NOT IN ('+condArr[f]+')');
        } else {
          whereArr.push('`'+ff[0]+'` '+ff[1]+' "'+condArr[f]+'"');
        }
      } else if (ff.length == 3) { // ff[0] 字段名, ff[1] 条件1, ff[2] 条件2, condArr[f] 值
        if (ff[2] == 'last') {
          let stamp = N(condArr[f]);
          let stampLimit = timLib.Stamp2Zero(stamp); // 今天的零时
          let hour = INT((stamp-stampLimit)/3600000);
          let subSql = 'SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(stamp/1000, "%Y-%m-%d '+hour+':00:00"))*1000 AS res FROM '+table;
          subSql += ' WHERE policy_id IN('+policyIds+') AND stamp<'+stampLimit;
          subSql += ' ORDER BY stamp DESC LIMIT 1';
          whereArr.push('`'+ff[0]+'` '+ff[1]+' ('+subSql+')');
        }
      } else { // 默认为=
        whereArr.push('`'+f+'` = "'+condArr[f]+'"');
      }
    }

    if (whereArr.length) return ' AND ' + whereArr.join(' AND ');
    else return '';
  }

  // 通过id查找策略数据
  // 搜库时未对uid做判断，所以需要鉴权处理
  // policyIds: 1,2,3 策略id字符串
  // limitStr = page,limit,margin  page从1开始，limit表示每页大小，margin表示裕量，三个参数均为非必须
  static findByPolicyIds(uid, table, policyIds, fieldStr='*', condArr=[], limitStr='', orderStr='')
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名

    // page,limit处理
    let page = 1;
    let limit = 9999;
    let margin = 0;
    let nArr = limitStr.split(',');
    if (nArr.length == 0) {}
    else if (nArr.length == 1) {page=1; limit=nArr[0]}
    else if (nArr.length == 2) {page=nArr[0]; limit=nArr[1]}
    else {page=nArr[0]; limit=nArr[1]; margin=nArr[2]}
    page = (page < 1) ? 1 : page;
    limit = (limit < 1) ? 1 : limit;
    margin = (margin < 0) ? 0 : margin;

    // 条件处理
    let condExp = this.conditonExpHandler(table, policyIds, condArr);

    // 排序处理
    const dir = ['DESC','ASC','desc','asc'];
    let order = orderStr.split(',');
    if (order.length != 2) order = ['stamp','DESC'];
    else if(!dir.includes(order[1])) order = [];

    // 内联字段处理
    let fields = fieldStr.split(',');
    for (let i in fields) {
      if (fields[i]=='*') fields[i] = 'a.*';
      else if (fields[i]=='id') fields[i] = 'a.id';
      else fields[i] = '`'+fields[i]+'`';
    }
    fields = fields.join(',');

    // SELECT a.*,b.uid FROM `dat_json_0039` AS a INNER JOIN `policy_id` AS b ON a.policy_id=b.id WHERE policy_id IN (605) AND uid=39
    return new Promise((resolve,reject) => {
      let uidSql = ' AND uid='+uid;
      let orderSql = order.length ? ' ORDER BY `'+order[0]+'` '+order[1] : '';
      let sql = 'SELECT '+fields+' FROM `'+table+'` AS a INNER JOIN `'+PCY_ID_TAB+'` AS b ON a.policy_id=b.id';
      sql += ' WHERE policy_id IN('+policyIds+')'+uidSql+condExp+orderSql+' LIMIT ?,?';
      console.log('sql:', sql, (page-1)*parseInt(limit), parseInt(limit)+parseInt(margin));
      this.query(sql, [(page-1)*parseInt(limit), parseInt(limit)+parseInt(margin)]).then((res)=>{
        resolve(res);
      }).catch(err=>{
        if (err.errno == EC.ER_NO_SUCH_TABLE) { // 表没建立时，返回空数组即可
          resolve([]);
          return;
        }
        console.log('['+policyIds+']['+table+'] findByPolicyIds faild:'+err.message);
        console.log('sql:',err.sql);
        reject(err);
      })
    })
  }

  // 通过id查找策略数据的总数
  // policyIds: 1,2,3 策略id字符串
  static totalOfPolicyIds(uid, table, policyIds, condArr=[])
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名

    // 条件处理
    let condExp = this.conditonExpHandler(table, policyIds, condArr);

    // SELECT COUNT(*) FROM `dat_json_0039` AS a INNER JOIN `policy_id` AS b ON a.policy_id=b.id WHERE policy_id IN (605) AND uid=39
    return new Promise((resolve,reject) => {
      let uidSql = ' AND uid='+uid;
      let sql = 'SELECT COUNT(*) FROM `'+table+'` AS a INNER JOIN `'+PCY_ID_TAB+'` AS b ON a.policy_id=b.id';
      sql += ' WHERE policy_id IN('+policyIds+')'+uidSql+condExp;
      this.query(sql).then((res)=>{
        resolve(res[0]['COUNT(*)']);
      }).catch(err=>{
        if (err.errno == EC.ER_NO_SUCH_TABLE) { // 表没建立时，返回0即可
          resolve(0);
          return;
        }
        console.log('['+policyIds+']['+table+'] totalOfPolicyIds faild:'+err.message);
        console.log('sql:',err.sql);
        reject(err);
      })
    })
  }

  // 通过产品id查找策略数据
  // 搜库时未对uid做判断，所以需要鉴权处理
  // limitStr = page,limit,margin  page从1开始，limit表示每页大小，margin表示裕量，三个参数均为非必须
  static findByProduct(uid, table, prod_id, limitStr='')
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名

    // page,limit处理
    let page = 1;
    let limit = 9999;
    let margin = 0;
    let nArr = limitStr.split(',');
    if (nArr.length == 0) {}
    else if (nArr.length == 1) {page=1; limit=nArr[0]}
    else if (nArr.length == 2) {page=nArr[0]; limit=nArr[1]}
    else {page=nArr[0]; limit=nArr[1]; margin=nArr[2]}
    page = (page < 1) ? 1 : page;
    limit = (limit < 1) ? 1 : limit;
    margin = (margin < 0) ? 0 : margin;

    // SELECT a.*,b.uid,b.prod_id FROM `dat_json_0039` AS a INNER JOIN policy_id AS b ON a.policy_id=b.id WHERE prod_id = 215 AND uid=39
    return new Promise((resolve,reject) => {
      let sql = 'SELECT a.*,b.prod_id FROM `'+table+'` AS a INNER JOIN `'+PCY_ID_TAB+'` AS b ON a.policy_id=b.id';
      sql += ' WHERE prod_id=? AND uid=? LIMIT ?,?';
      this.query(sql, [prod_id, uid, (parseInt(page)-1)*parseInt(limit), parseInt(limit)+parseInt(margin)]).then((res)=>{
        resolve(res);
      }).catch(err=>{
        if (err.errno == EC.ER_NO_SUCH_TABLE) { // 表没建立时，返回空数组即可
          resolve([]);
          return;
        }
        console.log('['+prod_id+']['+table+'] findByProduct faild:'+err.message);
        console.log('sql:', err.sql);
        reject(err);
      })
    })
  }

  // 通过产品id查找策略数据总数
  static totalByProduct(uid, table, prod_id)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名

    // SELECT COUNT(*) FROM `dat_json_0039` AS a INNER JOIN policy_id AS b ON a.policy_id=b.id WHERE prod_id = 215 AND uid=39
    return new Promise((resolve,reject) => {
      let sql = 'SELECT COUNT(*) FROM `'+table+'` AS a INNER JOIN `'+PCY_ID_TAB+'` AS b ON a.policy_id=b.id';
      sql += ' WHERE prod_id=? AND uid=?';
      this.query(sql, [prod_id, uid]).then((res)=>{
        resolve(res[0]['COUNT(*)']);
      }).catch(err=>{
        if (err.errno == EC.ER_NO_SUCH_TABLE) { // 表没建立时，返回空数组即可
          resolve(0);
          return;
        }
        console.log('['+prod_id+']['+table+'] totalByProduct faild:'+err.message);
        console.log('sql:', err.sql);
        reject(err);
      })
    })
  }

  // 统计表中策略数据总数
  static rcdCount(uid, table, policyId)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    return new Promise((resolve,reject) => {
      let sql = 'SELECT COUNT(*) AS total FROM `'+table+'` WHERE policy_id=?';
      this.query(sql, [policyId]).then((res)=>{
        if(res.length) resolve(res[0].total);
        else resolve(0);
      }).catch(err=>{
        if (err.errno == EC.ER_NO_SUCH_TABLE) { // 表没建立时，返回0即可
          resolve(0);
          return;
        }
        console.log('['+policyId+']['+table+'] getByPolicyId faild:'+err.message);
        console.log('sql:',err.sql);
        reject(err);
      })
    })
  }

  //根据时间戳获取记录
  static findByStamp(uid, table, pid, stamp)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    return new Promise((resolve,reject) => {
      let sql='SELECT * FROM `'+table+'` WHERE stamp=? AND policy_id=?';
      this.query(sql,[stamp,pid]).then(res=>{
        if (res.length) resolve(res[0]);
        else resolve(null);
      }).catch(err=>{
        console.log('findByStamp faild: ' + err.message)
        reject(err);
      })
    })
  }

  //根据时间段获取记录
  static findByDuration(uid, table, pid, start, end)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    return new Promise((resolve,reject) => {
      let sql='SELECT * FROM `'+table+'` WHERE policy_id=? AND stamp>=? AND stamp<?';
      this.query(sql,[pid,start,end]).then(res=>{
        resolve(res);
      }).catch(err=>{
        console.log('findByDuration faild: ' + err.message)
        reject(err);
      })
    })
  }

  // 获取今天之前，时间戳范围内的一条记录
  static findLastOneByStampScape(uid, table, pid, stamp, scapeSec, addrs=[])
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    return new Promise((resolve,reject) => {
      let stampLimit = timLib.Stamp2Zero(stamp); // 今天的零时
      let stampTop = stampLimit-(10*DAY_MSEC) // 十天前的零时，减少范围加快sql速度
      let stampTail = stamp%DAY_MSEC;
      let addrlist = addrs.length ? `AND addr IN (${addrs.join(',')})` : '';
      let sql = 'SELECT * FROM `'+table+'` WHERE policy_id=? '+addrlist+' AND stamp<? AND stamp>? AND stamp%?>=? AND stamp%?<=? ORDER BY stamp DESC LIMIT 1';
      this.query(sql, [pid, stampLimit, stampTop, DAY_MSEC, stampTail-(scapeSec*1000), DAY_MSEC, stampTail]).then(res=>{
        if (res.length) resolve(res[0]);
        else resolve(null);
      }).catch(err=>{
        console.log('findOneByStampLimit faild: ' + err.message)
        reject(err);
      })
    })
  }

  // 从指定表删除一条记录
  static deleteDataRcd(uid, table, policyId, id = 0)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    return new Promise((resolve,reject) => {
      let idSql = id > 0 ? ` AND id=${id}` : '';
      let sql = 'DELETE FROM `'+table+'` where policy_id=?'+idSql;
      this.query(sql, [policyId]).then(res=>{
        console.log('['+policyId+']['+table+'] del [id:'+id+'] affect:', res.affectedRows);
        resolve(res.affectedRows);
      }).catch(err=>{
        console.log('['+policyId+']['+table+'] del faild:' + err.message);
        reject(err);
      })
    })
  }

  //保留指定数量的记录，其他的清除
  static cleanByLimit(uid, table, policyId, retainNum)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    retainNum = parseInt(retainNum);
    return new Promise((resolve,reject) => { //limit n,m : n表示排序下标，m表示获取个数
      let sql = 'SELECT stamp FROM `'+table+'` WHERE policy_id=? ORDER BY stamp desc LIMIT ?,?';//每次读取限制之后的10个记录
      this.query(sql,[policyId,retainNum-1,retainNum/10]).then((res)=>{
        if ( res.length < retainNum/10 ) { resolve(0); } // 超出限制10个，就删除一次
        else {
          this.cleanByStamp(uid,table,policyId,res[0].stamp).then((affect)=>{
            //console.log('cleanLimit',table,policyId,affect);
            resolve(affect);
          })
        }
      }).catch(err=>{
        console.log('cleanLimit faild: ' + err.message)
        reject(err);
      })
    })
  }

  //删除指定时间戳之前的数据
  static cleanByStamp(uid, table, policyId, stamp)
  {
    table = FUN.CombTabName(uid, table); // 拼接用户数据表名
    return new Promise((resolve,reject) => {
      let sql = 'DELETE FROM `'+table+'` where policy_id=? AND stamp<=?';
      this.query(sql,[policyId,stamp]).then(res=>{
        console.log('[%d]['+table+'] clean',policyId,res.affectedRows,'rows');
        resolve(res.affectedRows);
      }).catch(err=>{
        console.log('[%d]['+table+'] clean faild:' + err.message);
        reject(err);
      })
    })
  }

  // alarm 表函数
  //新增报警记录
  static addAlaramLog(uid, hwid, policyId, sn, msg, stamp)
  {
    return this.newRcd(uid, DAT_ALARM_TAB, 'hwid,policy_id,sn,msg,stamp', [hwid,policyId,sn,msg,stamp]);
  }

  // policy_id 表的建立，主要是为了给其他所有dat表提供id外键
  // 所以只需要删除policy_id表的记录，就可以起到删除所有相关dat表的作用
  static recordPcyId(uid, policyId, prod_id)
  {
    return new Promise((resolve,reject) => { // table为sql关键字 不能做字段名 需加`符号
      let sql = 'INSERT IGNORE INTO '+PCY_ID_TAB+' (uid,id,prod_id) VALUES(?,?,?)';
      this.query(sql, [uid, policyId, prod_id+'']).then((res)=>{
        resolve(res.insertId);
      }).catch(err=>{
        console.log('['+uid+']['+PCY_ID_TAB+'] record INSERT faild:'+err.message);
        reject(err);
      })
    })
  }

  // policy_id 表函数
  // 查询policy对应的uid
  static queryPcyUid(policyId)
  {
    return new Promise((resolve,reject)=>{
      let sql = 'SELECt uid FROM '+PCY_ID_TAB+' WHERE id=?';
      this.query(sql, [policyId]).then((res)=>{
        if(res.length) resolve(res[0].uid);
        else resolve(-1)
      }).catch(err=>{
        console.log('['+uid+']['+PCY_ID_TAB+'] record INSERT faild:'+err.message);
        reject(err);
      })
    })
  }

  // policy_id 表函数
  // 删除policy id表中的策略记录，会触发外键删除策略所有数据
  static deletePcyId(uid, policyId)
  {
    return new Promise((resolve,reject)=>{
      let sql='DELETE FROM '+PCY_ID_TAB+' WHERE uid=? AND id=?';
      this.query(sql,[uid, policyId]).then(results=>{
          resolve(results.affectedRows);
      }).catch(err=>{
          console.log('['+uid+']['+PCY_ID_TAB+'] DELETE faild:'+err.message);
          reject(err);
      })
    })
  }

  // policy_id 表函数
  // 删除产品下的所有策略id，会触发外键删除策略所有数据
  static deletePcyIdByProd(uid, prod_id)
  {
    return new Promise((resolve,reject)=>{
      let sql='DELETE FROM '+PCY_ID_TAB+' WHERE uid=? AND prod_id=?';
      this.query(sql,[uid, prod_id]).then(results=>{
          resolve(results.affectedRows);
      }).catch(err=>{
          console.log('['+uid+']['+PCY_ID_TAB+'] DELETE faild:'+err.message);
          reject(err);
      })
    })
  }


}